var mysql = require('mysql');
var fs = require("fs");
var path = require("path");
// 加载配置文件
var mysqlConfig = JSON.parse(fs.readFileSync("../config/mysql.json", "utf8"));

// 使用sequelize ORM插件
var Sequelize = require("sequelize");
var sequelize = new Sequelize('node-mysql', 'root', 'Zhangkt0411', {
    host: 'localhost',
    port: '3306',
    dialect: 'mysql',
    pool: {
        max: 10,
        min: 1,
        idle: 10000
    },
    define: {
        timestamps: false,
        underscored: true,
        charset: 'utf8',
        collate: 'utf8_general_ci',
        freezeTableName: true
    }
});
exports.sequelize = function(){
    return sequelize;
}


exports.getConnection = function () {
    console.log('>>>开始连接数据库.........');
    return mysql.createConnection(mysqlConfig.connection);
}
exports.getPool = function () {
    console.log('>>>开始连接数据库.........');
    console.log('>>>创建连接池.........');
    return mysql.createPool(mysqlConfig.pool);
}
// 单个连接查询
exports.find= function (sql, params, callback) {
    var conn = exports.getConnection();
    conn.connect();
    console.log('>>>连接成功');
    var query = conn.query(sql, params, function (err, rows, fields) {
        if (err) throw err;
        callback(rows, fields);
    });
    console.log('>>>sql:' + query.sql);
    conn.end();
}
// 连接池查询
exports.query = function (sql, params, callback) {
    var pool = exports.getPool();
    pool.getConnection(function(err,conn){
        if(err){
            console.log('>>>获取连接失败<<<' + err);
            callback(err,null,null);
        }else{
            console.log('>>>获取连接成功<<<');
            if(params.sort && params.sort.length > 0){
                sql = sql + ' order by ';
                for(var i = 0;i < params.sort.length;i ++){
                    var sortCol = params.sort[i].sortCol;
                    var sortType = params.sort[i].sortType;
                    sql += sortCol + ' ' + sortType + ',';
                }
                sql = sql.substr(0,sql.length - 1);
            }
            if(params && params.pageSize && params.pageNum){
                sql = sql + ' limit ' + (params.pageNum - 1) * params.pageSize + ',' + params.pageSize;
            }
            var query = conn.query(sql, params, function (err, rows, fields) {
                if (err){
                    callback(err,rows, fields);;
                }else{
                    conn.release();
                    callback(null,rows, fields);
                }
            });
            console.log('>>>sql[' + query.sql + ']');
        }
    });
}
exports.queryTotalCount = function (sql, params, callback) {
    var pool = exports.getPool();
    pool.getConnection(function(err,conn){
        if(err){
            console.log('>>>获取连接失败<<<' + err);
            callback(err,null,null);
        }else{
            console.log('>>>获取连接成功<<<');
            var sql_count =  'select count(*) as totalCount from ('+sql+') as tmp_count';;
            var query = conn.query(sql_count, params, function (err, rows, fields) {
                if (err){
                    callback(err,rows, fields);;
                }else{
                    conn.release();
                    callback(null,rows, fields);
                }
            });
            console.log('>>>totalCount sql....[' + query.sql + ']');
        }
    });
}
